package demo1.dao.sql;

import demo1.dao.Mapper.userBuyMapper;
import demo1.pojo.address;
import demo1.pojo.gouwucar;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class userBuysql implements userBuyMapper {
    @Override
    public ResultSet selectAll(String userName,String userPwd) throws Exception {
        Connection conn = conn();
        String sql = "select * from usersbuy where userName = ? and userPwd = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,userName);
        pstmt.setString(2,userPwd);
        //执行sql
        return pstmt.executeQuery();
    }

    @Override
    public ResultSet select() throws Exception {
        Connection conn = conn();
        String sql = "select * from usersbuy";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //执行sql
        return pstmt.executeQuery();
    }

    @Override
    public void insertUserBuy(String userName, String userPwd) throws Exception {
        Connection conn = conn();
        String sql = "INSERT INTO usersbuy (userName, userPwd,money) VALUES (?, ?,?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, userName);
        pstmt.setString(2, userPwd);
        pstmt.setInt(3, 0);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateUserAddress(String userAddress, int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "update usersbuy set userAddress = ? where gouwuid = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, userAddress);
        pstmt.setInt(2, gouwuid);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateUserBuyPwd(String userPwd, int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "update usersbuy set userPwd = ? where gouwuid = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, userPwd);
        pstmt.setInt(2, gouwuid);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateUserBuyName(String userName, int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "update usersbuy set userName = ? where gouwuid = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, userName);
        pstmt.setInt(2, gouwuid);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public List<address> selectById(int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "select * from usersbuyaddress where gouwuid = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, gouwuid);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<address> addressList = new ArrayList<>();
        address address;
        while (rs.next()) {
            String usersAddress = rs.getString("usersAddress");//地址
            int addressID = rs.getInt("addressID");
            address = new address();
            address.setUsersAddress(usersAddress);
            address.setGouwuid(gouwuid);
            address.setAddressID(addressID);
            // 添加到列表中
            addressList.add(address);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return addressList;
    }

    @Override
    public address select(int addressID) throws Exception {
        Connection conn = conn();
        String sql = "select * from usersbuyaddress where addressID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, addressID);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        address address = new address();
        if(rs.next()) {
            String usersAddress = rs.getString("usersAddress");//地址
            address = new address();
            address.setUsersAddress(usersAddress);
            address.setGouwuid(rs.getInt("gouwuid"));
            address.setAddressID(addressID);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return address;
    }

    @Override
    public void updateAddress(String usersAddress,int addressID) throws Exception {
        Connection conn = conn();
        String sql = "update usersbuyaddress set usersAddress = ? where addressID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, usersAddress);
        pstmt.setInt(2, addressID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void deleteAddress(int addressID) throws Exception {
        Connection conn = conn();
        String sql = "delete from usersbuyaddress where addressID = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, addressID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void addAddress(String usersAddress,int gouwuid)throws Exception {
        Connection conn = conn();
        String sql = "insert into usersbuyaddress (usersAddress,gouwuid) values (?,?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, usersAddress);
        pstmt.setInt(2, gouwuid);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public List<address> searchAddress(String usersAddress, int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "select * from usersbuyaddress where gouwuid = ? and usersAddress LIKE ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, gouwuid);
        pstmt.setString(2, "%"+usersAddress+"%");
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<address> addressList = new ArrayList<>();
        address address;
        while (rs.next()) {
            int addressID = rs.getInt("addressID");
            address = new address();
            address.setUsersAddress(rs.getString("usersAddress"));
            address.setGouwuid(gouwuid);
            address.setAddressID(addressID);
            // 添加到列表中
            addressList.add(address);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return addressList;
    }

    @Override
    public void updateMoney(int money, int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "update usersbuy set money = ? where gouwuid = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, money);
        pstmt.setInt(2, gouwuid);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void insertGoods(int gouwuid, int price, String goodsMessage, String goodsName, int goodsGroup) throws Exception {
        Connection conn = conn();
        String sql = "insert into gouwucar (gouwuid,price,goodsMessage,goodsName,goodsGroup) values (?,?,?,?,?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, gouwuid);
        pstmt.setInt(2, price);
        pstmt.setString(3, goodsMessage);
        pstmt.setString(4, goodsName);
        pstmt.setInt(5, goodsGroup);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public int checkGouwucar(int gouwuid,String goodsName,String goodsMessage) throws Exception {
        Connection conn = conn();
        String sql = "select * from gouwucar where gouwuid = ? ";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,gouwuid);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()){
            //如果购物车中存在该商品
            if(Objects.equals(goodsName, rs.getString("goodsName")) && Objects.equals(goodsMessage, rs.getString("goodsMessage"))){
                rs.close();
                pstmt.close();
                conn.close();
                return 0;
            }
        }
        //如果不存在
        rs.close();
        pstmt.close();
        conn.close();
        return 1;
    }

    @Override
    public List<gouwucar> selectAll(int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "select * from gouwucar where gouwuid = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, gouwuid);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<gouwucar> gouwucarList = new ArrayList<>();
        gouwucar gouwucar;
        while (rs.next()) {
            String goodsName = rs.getString("goodsName");//地址
            String goodsMessage = rs.getString("goodsMessage");//地址
            int price = rs.getInt("price");
            int id = rs.getInt("id");
            int goodsGroup = rs.getInt("goodsGroup");
            gouwucar = new gouwucar();
            gouwucar.setId(id);
            gouwucar.setGoodsMessage(goodsMessage);
            gouwucar.setGoodsName(goodsName);
            gouwucar.setPrice(price);
            gouwucar.setGoodsGroup(goodsGroup);
            // 添加到列表中
            gouwucarList.add(gouwucar);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return gouwucarList;
    }

    @Override
    public List<gouwucar> selectAllByGoodsGroup(int goodsGroup,int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "select * from gouwucar where goodsGroup = ? and gouwuid = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, goodsGroup);
        pstmt.setInt(2, gouwuid);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<gouwucar> gouwucarList = new ArrayList<>();
        gouwucar gouwucar;
        while (rs.next()) {
            String goodsName = rs.getString("goodsName");//地址
            String goodsMessage = rs.getString("goodsMessage");//地址
            int price = rs.getInt("price");
            int id = rs.getInt("id");
            gouwucar = new gouwucar();
            gouwucar.setId(id);
            gouwucar.setGoodsMessage(goodsMessage);
            gouwucar.setGoodsName(goodsName);
            gouwucar.setPrice(price);
            gouwucar.setGoodsGroup(goodsGroup);
            // 添加到列表中
            gouwucarList.add(gouwucar);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return gouwucarList;
    }

    @Override
    public void deleteGoods(int id) throws Exception {
        Connection conn = conn();
        String sql = "delete from gouwucar where id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, id);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void deleteAll(int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "delete from gouwucar where gouwuid = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, gouwuid);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void deleteGoodsByName(int gouwuid, String goodsName, String goodsMessage) throws Exception {
        Connection conn = conn();
        String sql = "delete from gouwucar where gouwuid = ? and goodsName = ? and goodsMessage = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, gouwuid);
        pstmt.setString(2, goodsName);
        pstmt.setString(3, goodsMessage);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void deleteOrderForm(int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "delete from orderform where gouwuid = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, gouwuid);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void updateGroup(int goodsGroup, int id) throws Exception {
        Connection conn = conn();
        String sql = "update gouwucar set goodsGroup = ? where id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, goodsGroup);
        pstmt.setInt(2, id);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public gouwucar selectByGouwuid(int id) throws Exception {
        Connection conn = conn();
        String sql = "select * from gouwucar where id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, id);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        gouwucar gouwucar = null;
        if (rs.next()) {
            String goodsName = rs.getString("goodsName");//地址
            String goodsMessage = rs.getString("goodsMessage");//地址
            int price = rs.getInt("price");
            int goodsGroup = rs.getInt("goodsGroup");
            gouwucar = new gouwucar();
            gouwucar.setId(id);
            gouwucar.setGoodsMessage(goodsMessage);
            gouwucar.setGoodsName(goodsName);
            gouwucar.setPrice(price);
            gouwucar.setGoodsGroup(goodsGroup);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return gouwucar;
    }

    @Override
    public ResultSet selectAllBygouwuid(int gouwuid) throws Exception {
        Connection conn = conn();
        String sql = "select * from orderform where gouwuid = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, gouwuid);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        return rs;
    }

    @Override
    public int selectgoodsGroup(int id) throws Exception {
        Connection conn = conn();
        String sql = "select * from gouwucar where id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, id);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        if(rs.next()){
            return rs.getInt("goodsGroup");
        }
        rs.close();
        pstmt.close();
        conn.close();
        return -1;
    }

    @Override
    public void insertOrderForm(int money, Timestamp time, int gouwuid, int goodsGroup)throws Exception {
        Connection conn = conn();
        String sql = "insert into orderform (money,time,gouwuid,goodsGroup) values (?,?,?,?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,money);
        pstmt.setTimestamp(2, time);
        pstmt.setInt(3, gouwuid);
        pstmt.setInt(4, goodsGroup);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }
    public Connection conn()throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/project2";
        String user = "root";
        String password = "111214Cjj";

        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
}
